from flask import Flask, render_template, request, redirect, flash
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import asc, desc

app = Flask(__name__)

app.config.from_pyfile("config.py")

db = SQLAlchemy(app)

# 我们要创建一个第三方的表

test = db.Table("stu_tea_table", db.Column("stu_id", db.Integer, db.ForeignKey("students.id")),
                db.Column("tea_id", db.Integer, db.ForeignKey("teachers.id")))


class Student(db.Model):
    __tablename__ = "students"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True)


class Teacher(db.Model):
    __tablename__ = "teachers"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True)

    # 参1  指定一管理的类   secondary:多对多的第三方表  backref:反向指定一个字段   这个查询什么时候执行
    stus = db.relationship("Student", secondary="stu_tea_table", backref="teas", lazy="dynamic")


def create():
    db.create_all()


def add():
    stu1 = Student(name="航三学员")
    stu2 = Student(name="李四学员")
    stu3 = Student(name="王五学员")

    tea1 = Teacher(name="刘老师")
    tea2 = Teacher(name="张老师")

    db.session.add(stu1)
    db.session.add(stu2)
    db.session.add(stu3)

    db.session.add(tea1)
    db.session.add(tea2)

    # 创建关系 通过管理 外键插入
    tea1.stus.append(stu1)
    tea1.stus.append(stu2)
    tea1.stus.append(stu3)
    tea2.stus.append(stu2)
    tea2.stus.append(stu3)

    db.session.commit()


def query():
    tea1 = Teacher.query.filter(Teacher.id == 1).first()
    # 根据老师 找到所有的学生
    stus = tea1.stus.all()
    print(stus)

    # 找到学生
    stu = stus[2]
    # 通过学生找到老师   反向指定的字段 需要all()
    teas = stu.teas
    print(teas)


if __name__ == '__main__':
    query()
